import openpyxl
# from colorama import win32
from common.logging_handle import mylog
from common.path_handle import Config
import win32com.client as win32


class ExcelHandler():
    def __init__(self,object_path):
        self.file_path = object_path
        self.wb = openpyxl.load_workbook(self.file_path)


    def open_excel(self, sheet_name):
        # 获取指定sheet页
        sheet = self.wb[sheet_name]
        return sheet

    def get_headers(self, sheet_name):
        wb = self.open_excel(sheet_name)
        headers = []
        for i in wb[1]:
            headers.append(i.value)
            # print(i.value)
        return headers


    def read_excel(self):
        sheet = self.open_excel("sheet1")
        rows = list(sheet.rows)
        data = []
        for row in rows[1:]:
            row_data = []
            for cell in row:
                row_data.append(cell.value)
                # 将两个列表合成字典
                data_dict = dict(zip(self.get_headers("sheet1"), row_data))
            data.append(data_dict)
        return data

    def write_excel(self, file_path, sheet_name, row, cloumn, data):
        wb = openpyxl.load_workbook(file_path)
        sheet = wb[sheet_name]
        sheet.cell(row=row, column=cloumn).value = data
        wb.save(file_path)
        wb.close()

    def ready_excel(self,row_number):
        """读取包含引用公式的单元格"""
        excel = win32.gencache.EnsureDispatch('Excel.Application')
        excel.Visible = False
        excel.DisplayAlerts = False
        wb = excel.Workbooks.Open(self.file_path)
        ws = wb.Worksheets(1)
        row_data = ws.Rows(row_number).Value
        wb.Close(SaveChanges=False)
        excel.Quit()
        return row_data

    def wb_excel(self,row,data,sheet_name='sheet1',cloumn=8):
        try:
            ExcelHandler(self.file_path).write_excel(file_path=self.file_path,sheet_name=sheet_name,row=row,cloumn=cloumn,data=data)
            rdata=ExcelHandler(self.file_path).ready_excel(row)
            return rdata

        except Exception as e:
            mylog.error(e)
            raise e


if __name__ == '__main__':
    data = ExcelHandler(Config.fwmh_path).read_excel()
    print(data)

